#!/bin/bash

#通过start_time来指定清洗的数据时间范围（昨天：新增/更新）；
#通过end_time来指定获取当前有效的数据。
#清洗掉已删除的数据；
#判断学校id和学科id，把为空的字段统一转换为-1；

SQOOP_HOME=/usr/bin/sqoop

if [ $# -eq 0 ]
then
    DATESTR=`date -d "-1 day" +%Y-%m-%d`
else
    DATESTR=$1
fi

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--插入数据
insert into table itcast_dwd.itcast_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    rs.id as rid,
    rs.customer_id,
    rs.create_date_time,
    if((rs.itcast_school_id is null) or (rs.itcast_school_id = 0), -1, rs.itcast_school_id) as itcast_school_id,
    rs.deleted,
    rs.origin_type,
    if((rs.itcast_subject_id is null) or (rs.itcast_subject_id = 0), -1, rs.itcast_subject_id) as itcast_subject_id,
    substr(rs.create_date_time, 12, 2) hourinfo,
    if(rs.origin_type='NETSERVICE', '1', if(rs.origin_type='PRESIGNUP', '1', '0')) as origin_type_stat,
    substr(rs.create_date_time, 1, 4) yearinfo,
    substr(rs.create_date_time, 6, 2) monthinfo,
    substr(rs.create_date_time, 9, 2) dayinfo
from itcast_ods.customer_relationship rs
where rs.deleted = 0
  and substr(rs.start_time, 1, 10) = '${DATESTR}'--2021-01-05
  and rs.end_time = '9999-12-31';
"
